﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using NPOI.HSSF.UserModel;
using NPOI.HPSF;
using System.IO;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
namespace syc.Juece.Report
{
    public partial class 产量产值月情况表 : System.Web.UI.Page
    {
        protected HSSFWorkbook hssfworkbook;
        protected double nyoujihua = 0;//年油管计划          
        protected double ntaojihua = 0;//年套管计划
        protected double nN80taojihua = 0;//年套管计划
        protected double ytao = 0;//月套管
        protected double yN80tao = 0;//月套管
        protected double y1tao = 0;//一分厂月套管
        protected double y1N80tao = 0;//月套管
        protected double y2tao = 0;//二分厂月套管      
        protected double y2N80tao = 0;//二分厂月套管   
        protected double yyou = 0;//月油管
        protected double y2you = 0;//二分厂月油管
        protected double y5you = 0;//五分厂月油管
        protected double qntao = 0;//去年同期年套管
        protected double qnN80tao = 0;//去年同期年套管
        protected double qn1tao = 0;//去年一分厂年套管
        protected double qn2tao = 0;//去年二分厂年套管
        protected double qn1N80tao = 0;//去年一分厂年套管
        protected double qn2N80tao = 0;//去年二分厂年套管
        protected double qnyou = 0;//去年同期年油管
        protected double qn2you = 0;//去年二分厂年油管
        protected double qn5you = 0;//去年五分厂年油管
        protected double ntao = 0;//月套管
        protected double nN80tao = 0;//月套管
        protected double n1tao = 0;//一分厂月套管
        protected double n2tao = 0;//二分厂月套管    
        protected double n1N80tao = 0;//一分厂月套管
        protected double n2N80tao = 0;//二分厂月套管   
        protected double nyou = 0;//月油管
        protected double n2you = 0;//二分厂月油管
        protected double n5you = 0;//五分厂月油管
   
        
        protected double ytcz = 0;//本月套管产值
        protected double yN80tcz = 0;//本月套管产值
        protected double yycz = 0;//本月油管产值
        protected double tljcz = 0;//套管累计完成产值
        protected double N80tljcz = 0;//套管累计完成产值
        protected double yljcz = 0;//油管累计完成产值
        protected double qntljcz = 0;//套管累计完成产值
        protected double qnN80tljcz = 0;//套管累计完成产值
        protected double qnyljcz = 0;//油管累计完成产值
        protected int year;
        protected int month;
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                this.Month0.SelectedValue = DateTime.Today.Month.ToString();
                this.Year0.Text = DateTime.Today.Year.ToString();
            }
            Button_Query_Click(null, null);
        }
        protected void InitializeWorkbook()
        {

            FileStream file = new FileStream(HttpContext.Current.Server.MapPath("/JueCe/模板/产量产值完成情况表.xls"), FileMode.Open, FileAccess.Read);

            hssfworkbook = new HSSFWorkbook(file);

            DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
            dsi.Company = "产量产值完成情况表";
            hssfworkbook.DocumentSummaryInformation = dsi;

            SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
            si.Subject = "报表";
            si.Title = "产量产值完成情况表";
            hssfworkbook.SummaryInformation = si;
        }
        protected void WriteToFile()
        {
            Response.ContentType = "application/ms-excel";
            Response.AddHeader("content-disposition", "attachment; filename=\"" + DateTime.Today.ToShortDateString() + ".xls\"");
            hssfworkbook.Write(Response.OutputStream);
        }
        protected void Button_Save_Click(object sender, EventArgs e)
        {
            InitializeWorkbook();
            HSSFSheet sheet1 = hssfworkbook.GetSheet("产量产值完成情况表");
            Button_Query_Click(null, null);
            sheet1.GetRow(1).GetCell(0).SetCellValue(this.Year0.Text + "年" + this.Month0.Text + "月份产量产值完成情况表");
            sheet1.GetRow(4).GetCell(1).SetCellValue(ntaojihua);
            sheet1.GetRow(4).GetCell(2).SetCellValue((ytao / 1000).ToString("0.000"));
            sheet1.GetRow(4).GetCell(3).SetCellValue(ntao.ToString("0.000"));
            sheet1.GetRow(4).GetCell(4).SetCellValue(qntao.ToString("0.000"));
            sheet1.GetRow(4).GetCell(5).SetCellValue("");
            sheet1.GetRow(4).GetCell(6).SetCellValue(ytcz.ToString("0.00"));
            sheet1.GetRow(4).GetCell(7).SetCellValue(tljcz.ToString("0.00"));
            sheet1.GetRow(4).GetCell(8).SetCellValue(qntljcz.ToString("0.00"));

            sheet1.GetRow(5).GetCell(1).SetCellValue(nN80taojihua);
            sheet1.GetRow(5).GetCell(2).SetCellValue((ytao / 1000).ToString("0.000"));
            sheet1.GetRow(5).GetCell(3).SetCellValue(nN80tao.ToString("0.000"));
            sheet1.GetRow(5).GetCell(4).SetCellValue(qnN80tao.ToString("0.000"));
            sheet1.GetRow(5).GetCell(5).SetCellValue("");
            sheet1.GetRow(5).GetCell(6).SetCellValue(yN80tcz.ToString("0.00"));
            sheet1.GetRow(5).GetCell(7).SetCellValue(N80tljcz.ToString("0.00"));
            sheet1.GetRow(5).GetCell(8).SetCellValue(qnN80tljcz.ToString("0.00"));


            sheet1.GetRow(6).GetCell(1).SetCellValue(nyoujihua);
            sheet1.GetRow(6).GetCell(2).SetCellValue((yyou / 1000).ToString("0.000"));
            sheet1.GetRow(6).GetCell(3).SetCellValue(nyou.ToString("0.000"));
            sheet1.GetRow(6).GetCell(4).SetCellValue(qnyou.ToString("0.000"));
            sheet1.GetRow(6).GetCell(5).SetCellValue("");
            sheet1.GetRow(6).GetCell(6).SetCellValue(yycz.ToString("0.00"));
            sheet1.GetRow(6).GetCell(7).SetCellValue(yljcz.ToString("0.00"));
            sheet1.GetRow(6).GetCell(8).SetCellValue(qnyljcz.ToString("0.00"));

            WriteToFile();
            Response.End();
        }
       
        protected void Button_Query_Click(object sender, EventArgs e)
        {
            year = int.Parse(this.Year0.Text);
            month = int.Parse(this.Month0.Text);           
            SqlConnection Conn = new SqlConnection();
            Conn.ConnectionString = ConfigurationManager.ConnectionStrings["SCMConnectionString1"].ConnectionString;
            try
            {   
                Conn.Open();
                SqlCommand Cmd = new SqlCommand();
                Cmd.Connection = Conn;
                //获得年计划
                Cmd.CommandText = @"select (isnull(二分厂产品1,0)+isnull(五分厂产品1,0)) as 月油管计划,(isnull(一分厂产品4,0)) as 月N80套管计划,(isnull(一分厂产品2,0)+isnull(二分厂产品2,0)) as 月套管计划 from 生产计划 where 年度='" + year + "' and 月份=0";
                SqlDataReader Reader = Cmd.ExecuteReader();
                if (Reader.Read())
                {
                    nyoujihua = Reader.GetDouble(0);
                    ntaojihua = Reader.GetDouble(1);
                    nN80taojihua = Reader.GetDouble(2);
                }
                Reader.Close();
                //月油套管量
                string sql1 = "select dbo.[产品本期收入](*年份*,*月份*,1,0),dbo.[产品本期收入](*年份*,*月份*,2,0)";
                sql1 = sql1.Replace("*年份*", year.ToString());
                sql1 = sql1.Replace("*月份*", month.ToString());
                Cmd.CommandText = sql1;
                Reader = Cmd.ExecuteReader();
                if (Reader.Read())
                {
                    yyou = double.Parse(Reader.GetDouble(0).ToString());
                    ytao = double.Parse(Reader.GetDouble(1).ToString());
                }
                Reader.Close();    
                //去年油管套管量   
                string sql = "select dbo.[产品本期结存](*年份*,*月份*,1,0),dbo.产品本期结存(*年份*,*月份*,2,0),dbo.产品本期结存(*年份*,*月份*,4,0)";
                sql = sql.Replace("*年份*", (year - 1).ToString());
                sql = sql.Replace("*月份*", month.ToString());
                Cmd.CommandText = sql;
                Reader = Cmd.ExecuteReader();
                if (Reader.Read())
                {
                    qnyou = double.Parse(Reader.GetDouble(0).ToString());
                    qntao = double.Parse(Reader.GetDouble(1).ToString());
                    qnN80tao = double.Parse(Reader.GetDouble(2).ToString());
                }
                Reader.Close();
                //当年产品某月止累计
                string sql2 = "select dbo.[产品本期结存](*年份*,*月份*,1,0),dbo.产品本期结存(*年份*,*月份*,2,0),dbo.产品本期结存(*年份*,*月份*,4,0)";
                sql2 = sql2.Replace("*年份*", year.ToString());
                sql2 = sql2.Replace("*月份*", month.ToString());
                Cmd.CommandText = sql2;
                Reader = Cmd.ExecuteReader();
                if (Reader.Read())
                {
                    nyou = double.Parse(Reader.GetDouble(0).ToString());
                    ntao = double.Parse(Reader.GetDouble(1).ToString());
                    nN80tao = double.Parse(Reader.GetDouble(2).ToString());
                }
                Reader.Close();
              
                //本月油套管完成值
                Cmd.CommandText = @" 
                declare @ytcz float
                declare @yycz float
                select @ytcz=isnull(sum(实际产值),0) from 工业总产值数据 where 年份='" + year + "' and 月份='" + month + "' and 产品类型ID=2 select @yycz=isnull(sum(实际产值),0) from 工业总产值数据 where 年份='" + year + "' and 月份='" + month + "' and 产品类型ID=1 select @ytcz,@yycz";
                Reader = Cmd.ExecuteReader();
                if (Reader.Read())
                {
                    //y套管产值
                    ytcz = Reader.GetDouble(0);
                    //油管产值
                    yycz = Reader.GetDouble(1);
                }
                Reader.Close();
                //油套管累计完成值
                Cmd.CommandText = @" 
                declare @tljcz float
                declare @yljcz float
                select @tljcz=isnull(sum(实际产值),0) from 工业总产值数据 where 年份='" + year + "' and 月份 between '1' and '" + month + "' and 产品类型ID=2 select @yljcz=isnull(sum(实际产值),0) from 工业总产值数据 where 年份='" + year + "' and 月份 between '1' and '" + month + "' and 产品类型ID=1 select @tljcz,@yljcz";
                Reader = Cmd.ExecuteReader();
                if (Reader.Read())
                {
                    //y套管产值
                    tljcz = Reader.GetDouble(0);
                    //油管产值
                    yljcz = Reader.GetDouble(1);
                }
                Reader.Close();
                //去年油套管累计完成值
                Cmd.CommandText = @" 
                declare @qntljcz float
                declare @qnN80tljcz float
                declare @qnyljcz float
                select @qntljcz=isnull(sum(实际产值),0) from 工业总产值数据 where 年份='" + (year - 1) + "' and 月份 between '1' and '" + month + "' and 产品类型ID=2 select @qnyljcz=isnull(sum(实际产值),0) from 工业总产值数据 where 年份='" + (year - 1) + "' and 月份 between '1' and '" + month + "' and 产品类型ID=1 select @qnN80tljcz=isnull(sum(实际产值),0) from 工业总产值数据 where 年份='" + (year - 1) + "' and 月份 between '1' and '" + month + "' and 产品类型ID=4 select @qntljcz,@qnyljcz,@qnN80tljcz";
                Reader = Cmd.ExecuteReader();
                if (Reader.Read())
                {
                    //y套管产值
                    qntljcz = Reader.GetDouble(0);
                    //油管产值
                    qnyljcz = Reader.GetDouble(1);
                    qnN80tljcz = Reader.GetDouble(2);
                }
                Reader.Close();              
                Reader.Dispose();
                Cmd.Dispose();
            }
            catch (Exception ex)
            {
                Conn.Close();   
                throw ex;
            }            
            finally
            {
                Conn.Close();              
                Conn.Dispose();
            }
            
        }       
    }
}
